ODPS SQL 大全

bigint char_matchcount(string str1, string str2)

用途：用于计算字符串str1中的字符有多少个出现在字符串str2中；

string char(bigint ascii)

用途：将一个给定的ASCII码转换为字符

3.concat

string concat(string a , string b …)

用途：将多个字符串凭凑成为一个字符串

string get_json_object( string json, string path)

用途：在一个标准的json字符串中，按照path抽取指定的字符串

bigint instr(string str1, string str2, bigint strat_position, bigint nth_appearance)

用途：计算一个字符串str1在字符串str2中的位置

for example:

instr(‘tech on the net’, ‘e’,1 ,2) = 11

boolean is_encoding(string str, string from_encoding, string to_encoding)

用途：用于判断一个字符串是否可以从一个字符编码变成另外一种字符编码

for example:

is_encoding(‘测试’， ‘utf-8’, ‘GBK’) = true;

keyvalue(string srcstr, string split1, string split2, string key);

keyvalue(string srcstr, string key) //split1=’;’ , split2 = ‘:’

用途：用两个分隔符分割后的键值对，给一个键返回一个数值；默认的两个分隔符为封号和冒号

for example:

keyvalue(‘0:1\;1:2’, 1) = 2

bigint length(string str)

用途：返回一个字符串的长度

bigint lengthb(string str)

用途：返回一个字符串有多少个字节，比如说，一个中文文字就是两个字节

string md5(string value)

用途：计算输入字符串value的md5值

string regexp_extract(string source, string pattern, bigint occurrence)

用途:

12.regexp_instr(不懂)

13.regexp_replace

14.regexp_substr

15.regexp_count

16.split_part

string split_part(string str, string separator, bigint start[, bigint end])

用途：按照分隔符separator分割字符串，返回从start开始到end结束的字符串

string substr(string str, bigint start_position[, bigint length])

用途：返回字符串str从start_position开始，长度为length的字符串。

string tolower(string source)

string toupper(string source)

string to_char(bigint/boolean/double/datetime value)

string trim(string str)

用途：将指定字符串去除左右空格

string ltrim(string str)

用途：将指定字符串去除左空格

string rtrim(string str)

用途：将指定字符串去除左空格

string reverse(string str)

string space(bigint n)

用途：空格字符串函数，返回长度为n的空字符串。

string repeat(string str, bigint n)

用途：返回重复n次后的str字符串。

开窗函数，支持这种开窗函数的有avg/count/max/min/stddev/sum，一般都是用于分区表的操作。

bigint count([distinct] expr) over (partition by col1), [col2…] order by col1[asc|desc] window_clause

计算计数值

select user_id,

count(user_id) over (partition by user_id) as count

from test_src;

select user_id

count(user_id) over (partirion by user_id order by user_id) as count

from test_src;

avg([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算平均值

max([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算最大值|最小值

double median([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

decimal median([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算中位数

总体标准差

double stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

decimal stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

样本标准差

double stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

decimal stddev([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

计算汇总值

sum([distinct] expr) over (partition by col1)[, col2…] [order by col1 [asc|desc]] [,col2[asc|desc]…]] [window_clause])

bigint dense_rank() over(partition by col1[, col2…]) order by col1[asc|desc] [col2[asc|desc]]

计算连续排名。

bigint rank() over (partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…])

计算排名。col2相同的行数获得排名顺序下降

lag(expr, bigint offset, default) over (partition by col1[, col2…])

order by col1[asc|desc][, col2[asc|desc]…])

按照偏移量取当前行之前第几行的值，如果当前行号为rn，则取行号为rn-offset的值。

lead(expr, bigint offset, default) over (partition by col1[, col2…])

order by col1[asc|desc][, col2[asc|desc]…])

按照偏移量取当前行之后第几行的值，如果当前行号为rn，则取行号为rn+offset的值。

percent_rank() over partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…]

计算一组数据中某行的相对排名

row_number() over partition by col1[, col2…] order by col1 [asc|desc][, col2[asc|desc]…]

计算行号，从1开始

boolean cluster_sample(bigint x[, bigint y]) over(partition by col1[, col2…])

分组抽样

例如：有两个实验分组，group_a和group_b，每个实验分组的数值value都是几千个，现在要从a组和b组中随机抽样10%,用sql语句如何实现？

select group_id, value

from(

select group_id, value, cluster_sample(10,1) over (partition by group_id) as flag

from tbl ) sub

where flag = true;

bigint count([distinct|all value)

计算记录数,可以与聚合函数搭配使用，类似map_reduce里面的combiner

double avg(double value)

decimal avg(double value)

计算平均数；如果value是bigint,会隐式转换为double类型后参与运算

max(value)

计算最大值，value是什么类型，返回值就是什么类型

min(value)

计算最小值，value是什么类型，返回值就是什么类型

double median(double number)

decimal median(decimal mumber)

计算中位数

double stddev(double number)

decimal stddev(decimal number)

计算总体标准差

double stddev_samp(double number)

decimal stddev_samp(decimal number)

计算样本标准差

sum(value)

计算汇总值，value是什么类型，返回什么类型

string wm_concat(string separator, string str)

用指定的分隔符，把str衔接起来

cast(expr as )

将表达式的结果转换为指定的类型，例如：

cast(‘120’ as string)

cast(double as bigint)

cast(string as bigint)

cast(string as datetime) 或者 cast(datetime as string) 会采用默认的日期格式yyyy-mm-dd hh:mm:ss

coalesce(expr1, expr2, …)

返回参数列表中第一个非NULL的值，如果列表中全部都是NULL则返回NULL

decode(expression, search, result[, search, result]…[,default])

实现if-then-else分支选择的功能

以下两个案例是等值的

select

decode(customer_id

1, ‘taobao’,

2, ‘alipay’,

3, ‘aliyun’,

NULL, ‘N/A’

‘others’) as result

from sales_detail;

select

(if customer_id := 1 then

result := ‘taobao’;

elseif customer_id := 2 then

result := ‘alipay’;

elseif customer_id := 3 then

result := ‘aliyun’;

elseif customer_id := null then

result := ‘N/A’ )

else

result := ‘others’

end if;

) from sales_detail;

greatest(var1, var2, …)

返回输入参数中最大的一个

ordinal(bigint nth, var1, var2, …)

将输入变量从小到大排序后，返回nth指定的位置的值。

least(var1, var2, …)

返回输入参数中最小的一个

uuid()

返回一个随机ID

boolean sample(x, y, column_name)

对所有读入的column_name的值，哈希x份，取第y份。其中y和colum_name同时存在或者同时省略

case value

when (condition1) then result1

when (condition2) then result2

…

else resultn

end

case

when (condition1) then result1

when (condition2) then result2

…

else resultn

end

if(testcondition, valuetrue, valuefalseornull)

判断testcondition是否为真，真取第一个数值，否则取第二个数值。

datetime dateadd(datetime date, bigint delta, string datepart)；

用途：按照指定的单位datepart和幅度delta修改date的值

bigint datediff(datetime date1, datetime date2, string datepart);

用途:计算两个时间date1, date2 在指定时间单位datepart的差值

bigint datepart(datetime date, string datepart)

用途：提取日期date中指定的时间单位datepart的值

datetime datetrunc(datetime date, string datepart)

用途：返回日期date被截取指定时间单位datepart后的日期值

datetime from_unixtime(bigint unixtime)

用途：将数字型的unix时间日期值unixtime 转为日期值

bigint unix_timestamp(datetime date)

用途：将日期值转换成unixtime数值

datetime gettime()

用途：获取计算的当前日期值

boolean isdate(string date, string format)

用途：判断一个日期字符能否基于对应的格式转换为一个日期值，如果可以返回true，否则返回false

datetime lastday(datetime date)

用途：取date当月的最后一天，截取到天，时分秒部分为00：00：00

datetime to_date(string date, string format)

用途：将一个字符串date按照format的指定的格式转换为日期值

string to_char(datetime date, string fromat)

用途：将一个日期值按照指定的格式转换为字符串

bigint weekday(datetime date)

用途：返回date 日期当前周的第几天

bigint weekofyear(datetime date)

用途：返回date在那一年中的第几周

double abs(double number)

bigint abs(bigint number)

decimal abs(decimal number)

返回绝对值

select abs(id) from tb1;

double acos(double number)

decimal acos(decimal number)

计算number的反余玄函数

double asin(double number)

decimal asin(decimal number)

计算number的反正玄函数

double atan(double number)

反正切函数

bigint ceil(double number)

bigint ceil(decimal number)

返回不小于number的最小整数

string conv(string input, bigint from_base, bigint to_base)

进制转换函数

例如：

conv(‘1111111001010101’, 2, 10)

double cos(double number)

decimal cos(decimal number)

计算number的余玄函数

double cosh(double number)

decimal cosh(decimal number)

计算number的双余玄函数

double cot(double number)

decimal cot(decimal number)

计算number的余切函数

double exp(double number)

decimal exp(decimal number)

计算number的双余玄函数

bigint floor(double number)

bigint floor(decimal number)

向下取整，返回比number小的整数值

floor(1.2) = 1

floor(-1.2) = -2

floor(0.1) = 0

double ln(double number)

decimal ln(decimal number)

计算number的自然对数

double log(double base, double x)

decimal log(decimal base, decimal x)

返回以base为底的x的对数

double pow(double x, double, y)

decimal pow(decimal x, decimal, y)

返回x的y次方

double rand(bigint seed)

返回以seed为种子的double随机数，返回值区间是的0-1；

double round(double number, [bigint decimal_places])

decimal round(decimal number, [bigint decimal_places])

四舍五入到指定小数点

decimal_places可以是负数，负数会从小数点向左开始计数，并且不保留小数部分；

round(123.345, -2) = 100.0

round(123.345, -4) = 0.0

sign(x)

x为正值，返回1.0

x为负值，返回-1.0

当x为0， 返回0.0

当x为空，抛出异常

double sin(double number)

正玄函数

double sinh(double number)

decimal sinh(decimal number)

双曲正玄函数

double sqrt(double number)

decimal sqrt(decimal number)

计算平方根

double tan(double number)

decimal tan(decimal number)

计算正切函数

double tanh(double number)

decimal tanh(decimal number)

计算双曲正切函数

double trunc(double number[, bigint decimal_places])

decimal trunc(decimal number[, bigint decimal_places])

将输入值number截取到指定小数点的位置，截取掉的部分补0，decimal_places也可以是负数，如果是负数，从小数点向左开始截取，并且不保留小数部分；如果decimal_places超过了整数部分长度，返回0

trunc(125.815) = 125.0

trunc(125.815,0)=125.0

trunc(125.815,1)=125.8

trunc(125.815,-1)=120.0